﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;   //contains classes to configure DB login setting
using System.Data.SqlClient;  
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using AssignmentSWEN3.App_Code;

namespace AssignmentSWEN3.App_Code
{
    public class DataManager
    {
        public static bool Login(int staffOID, string password)
        {
            bool success = false;

            SqlConnection conn = null;
            try
            {
                //connect to databse
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
                conn.Open();

                //prepare SQL command to get all genre types from databse
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM  Staff WHERE StaffOID=@staffOID AND Password=@password";
                comm.Parameters.AddWithValue("@staffOID", staffOID);
                comm.Parameters.AddWithValue("@password", password);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    success = true;
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return success;
        }

        public static int InsertStaff(Staff m)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;

            try
            {
                //connect to database
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
                conn.Open();

                //prepare sql command to update
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Staff(StaffOID, Name, Dob, BankAccNum, Address, Email, Password, PhoneNum, DutyType) values (@staffOID, @name, @dob, @bankAccNum, @address, @email, @password, @phoneNum, @dutyType)";

                //pass into the sql command to update staff
                comm.Parameters.AddWithValue("@staffOID", m.StaffOID);
                comm.Parameters.AddWithValue("@name", m.Name);
                comm.Parameters.AddWithValue("@dob", m.Dob);
                comm.Parameters.AddWithValue("@bankAccNum", m.BankAccNum);
                comm.Parameters.AddWithValue("@address", m.Address);
                comm.Parameters.AddWithValue("@email", m.Email);
                comm.Parameters.AddWithValue("@password", m.Password);
                comm.Parameters.AddWithValue("@phoneNum", m.PhoneNum);
                comm.Parameters.AddWithValue("@dutyType", m.DutyType);

                //execute  the command
                rowsInserted = comm.ExecuteNonQuery();// for insert, update, delete
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static Staff GetStaffByID(int staffOID)
        {
            Staff m = new Staff();
            SqlConnection conn = null;

            try
            {
                //establish database connection
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
                conn.Open();
                //prepare SQL command and execute rge statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff where StaffOID=@staffOID";
                comm.Parameters.AddWithValue("@staffOID", staffOID);
                //read the data
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m.StaffOID = (int)dr["staffOID"];
                    m.Name = (string)dr["name"];
                    m.Dob = (DateTime)(dr["dob"]);
                    m.BankAccNum = (string)dr["bankAccNum"];
                    m.Address = (string)dr["address"];
                    m.Email = (string)dr["email"];
                    m.Password = (string)dr["password"];
                    m.PhoneNum = (int)(dr["phoneNum"]);
                    m.DutyType = (string)dr["dutyType"];
                }

            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static Staff GetStaffByName(string name)
        {
            SqlConnection conn = null;
            Staff m = null;

            try
            {
                //establish database connection
                conn = new SqlConnection();
                conn.ConnectionString =
                ConfigurationManager.ConnectionStrings["db"].ConnectionString;
                conn.Open();
                //prepare SQL command and execute rge statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE Name=@name";
                comm.Parameters.AddWithValue("@name", name);
                //read the data
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    m = new Staff();
                    m.StaffOID = (int)dr["staffOID"];
                    m.Name = (string)dr["name"];
                    m.Dob = (DateTime)(dr["dob"]);
                    m.BankAccNum = (string)dr["bankAccNum"];
                    m.Address = (string)dr["address"];
                    m.Email = (string)dr["email"];
                    m.Password = (string)dr["password"];
                    m.PhoneNum = (int)dr["phoneNum"];
                    m.DutyType = (string)dr["dutyType"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static int UpdateStaff(Staff up)
        {
            int rowsupdated = 0;
            //connect to database;
            SqlConnection conn = null;

            try
            {
                //establish database connection   //connect to database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["db"].ConnectionString;
                conn.Open();

                //prepare SQL command to update music;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET StaffOID=@staffOID, Name=@name, Dob=@dob, BankAccNum=@bankAccNum, Address=@address, Email=@email, Password=@password, PhoneNum=@phoneNum, DutyType=@dutyType WHERE StaffOID=@staffOID";
                // pass data into SQL command

                comm.Parameters.AddWithValue("@staffOID", up.StaffOID);
                comm.Parameters.AddWithValue("@name", up.Name);
                comm.Parameters.AddWithValue("@dob", up.Dob);
                comm.Parameters.AddWithValue("@bankACcNum", up.BankAccNum);
                comm.Parameters.AddWithValue("@address", up.Address);
                comm.Parameters.AddWithValue("@email", up.Email);
                comm.Parameters.AddWithValue("@password", up.Password);
                comm.Parameters.AddWithValue("@phoneNum", up.PhoneNum);
                comm.Parameters.AddWithValue("@dutyType", up.DutyType);


                //execute the command
                rowsupdated = comm.ExecuteNonQuery(); // for insert, update and delete
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteStaff(string name)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Staff WHERE @name=name";
                comm.Parameters.AddWithValue("@name", name);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int DeleteStaff(int id)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Staff WHERE @staffOID=staffOID";
                comm.Parameters.AddWithValue("@staffOID", id);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

    }
}
